import os
import re
import traceback

import pandas as pd

pattern = r"[`']([^`']+)[`']"
tradition_ = 'COMMENT'
common_part = ' STRING COMMENT '
last_line_str = ' PARTITIONED BY (As BIGINT) STORED AS PARQUET;'


def modify_row(row):
    sql_row = row[0]
    if 'CREATE TABLE' in sql_row:
        return row
    if 'COMMENT=' in sql_row:
        comment = sql_row.split('COMMENT=')[-1].replace(';', '').replace(' ', '')
        cut_sql = ')COMMENT ' + comment + last_line_str
        row[0] = cut_sql
        return row
    cut_result = re.findall(pattern, sql_row)
    if len(cut_result) > 1 and tradition_ in sql_row:
        cut_sql = cut_result[0] + common_part + f"'{cut_result[-1]}'" + ','
        row[0] = cut_sql
    return row


current_dir = os.getcwd()
to_dir = os.path.join(current_dir, "results_dir")
print('current_dir:', current_dir)
print('results_dir:', to_dir)
if not os.path.exists(to_dir):
    os.mkdir(to_dir)
try:
    for file in os.listdir(current_dir):
        if file.endswith(".xlsx") or file.endswith(".xls"):
            file_path = os.path.join(current_dir, file)
            to_file_path = os.path.join(to_dir, file)
            df = pd.read_excel(file_path)
            df.apply(modify_row, axis=1)
            df.to_csv(to_file_path, index=False)
except:
    print(traceback.format_exc())
